# -*- coding:utf-8 -*-
"""
Python 3.7
Author：刘爽、何若溥
Time：2022/08/23
"""
from datetime import datetime, date
import pandas as pd

from common.mysqlUtil import MysqlUntil
from common.path import CONFIGPATH
from common.fileUtil import getProperties



def tuple2dic(t):
    if t == ():
        list1 = []
        list2 = []
    else:
        list1, list2 = zip(*t)
    return list1, list2


class BugDBConn:
    def __init__(self, project_id, db_host, db_user, db_passwd, db_port, db_name):
        self.conn = MysqlUntil(db_host, db_user, db_passwd, db_port, db_name)
        self.project_id = project_id
        print(db_host)

    # 按照bug状态查询数据
    def bugStatusData(self):
        bug_status_sql = f"SELECT status, COUNT(*) FROM zt_bug WHERE product={self.project_id} AND module=5331 AND deleted=1 GROUP BY status;"
        bug_status_data = self.conn.searchData(bug_status_sql)
        return tuple2dic(bug_status_data)

    # 查询激活状态的bug，按照指派给统计
    def bugActiveAssignData(self):
        active_assign_sql = f"SELECT assignedTo, COUNT(*) FROM zt_bug WHERE product={self.project_id} AND module=5331 AND status=\"active\" AND deleted=1 GROUP BY assignedTo;"
        bug_active_assign_data = self.conn.searchData(active_assign_sql)
        list1, list2 = tuple2dic(bug_active_assign_data)
        assign_list = []
        if list1:
            if len(list1) == 1:
                user_sql = f"SELECT realname FROM zt_user WHERE account IN {list1[0]}"
            else:
                user_sql = f"SELECT realname FROM zt_user WHERE account IN {list1}"
            user_data = self.conn.searchData(user_sql)
            for item in user_data:
                assign_list.append(item[0])
        return assign_list, list2

    # 查询激活状态的bug，按照严重等级统计
    def bugActiveSeverityData(self):
        active_severity_sql = f"SELECT severity, COUNT(*) FROM zt_bug WHERE product={self.project_id} AND status=\"active\" AND module=5331 AND deleted=1 GROUP BY severity;"
        bug_active_severity_data = self.conn.searchData(active_severity_sql)
        return tuple2dic(bug_active_severity_data)

    # 查询当天新增问题、当日解决问题、剩余未解决问题个数
    def todayBugData(self):
        zeroToday = str(datetime.strptime(str(date.today()), '%Y-%m-%d'))
        # 当天新增问题数
        td_new_bug_sql = f"SELECT COUNT(*) FROM zt_bug where product={self.project_id} AND module=5331 AND openedDate >= \"{zeroToday}\" AND deleted=1;"
        td_new_bug_count = self.conn.searchData(td_new_bug_sql)[0][0]
        # print(td_new_bug_count)

        # 当日解决问题数
        td_resolve_bug_sql = f"SELECT COUNT(*) FROM zt_bug where product={self.project_id} AND module=5331 AND resolvedDate >= \"{zeroToday}\" AND deleted=1;"
        td_resolve_bug_count = self.conn.searchData(td_resolve_bug_sql)[0][0]
        # print(td_resolve_bug_count)

        # 剩余未修复问题数
        td_unResolve_bug_sql = f"SELECT COUNT(*) FROM zt_bug where product={self.project_id} AND module=5331 AND status=\"active\" AND deleted=1;"
        td_unResolve_bug_count = self.conn.searchData(td_unResolve_bug_sql)[0][0]
        # print(td_unResolve_bug_count)

        return td_new_bug_count, td_resolve_bug_count, td_unResolve_bug_count

    # 查询未修复bug列表
    def activeBugList(self):
        active_sql = f"SELECT title, assignedTo FROM zt_bug WHERE product={self.project_id} AND module=5331 AND status=\"active\" AND deleted=1"
        bug_active_data = self.conn.searchData(active_sql)
        if bug_active_data:
            bug_active_dataframe = pd.DataFrame(data=bug_active_data, columns=["title", "assignedTo"])
            list1 = tuple(bug_active_dataframe["assignedTo"].to_list())
            assign_list = []
            if list1:
                for item in list1:
                    user_sql = f"SELECT realname FROM zt_user WHERE account='{item}';"
                    user_data = self.conn.searchData(user_sql)
                    assign_list.append(user_data[0][0])
                assign_series = pd.Series(assign_list)
                bug_active_dataframe.insert(2, "assignedToName", assign_series)
                bug_active_dataframe.drop(columns="assignedTo", inplace=True)
                return bug_active_dataframe
            return None
        return None


if __name__ == '__main__':
    db = getProperties(CONFIGPATH)
    bd = BugDBConn(39, db["db_host"], db["db_user"], db["db_passwd"], int(db["db_port"]), db["db_name"])
    a, b = bd.bugActiveAssignData()
    print(a, b)

    bd.conn.closeDB()
